from flask import render_template, request, jsonify, session
from models import HouseInfoFinal, UserActual, db
from sqlalchemy import func, and_
from . import houses_bp

@houses_bp.route('/house/search', methods=['GET', 'POST'])
def search_house():
    if request.method == 'POST':
        region = request.form.get('region')
        house_type = request.form.get('house_type')
        min_price = request.form.get('min_price')
        max_price = request.form.get('max_price')
        page = int(request.form.get('page', 1))

        # 构建查询条件
        query = HouseInfoFinal.query

        if region:
            query = query.filter(HouseInfoFinal.region.like(f'%{region}%'))

        if house_type:
            query = query.filter(HouseInfoFinal.house_type.like(f'%{house_type}%'))

        if min_price:
            query = query.filter(HouseInfoFinal.price >= float(min_price))

        if max_price:
            query = query.filter(HouseInfoFinal.price <= float(max_price))

        # 分页查询
        per_page = 20
        pagination = query.paginate(page=page, per_page=per_page, error_out=False)
        houses = pagination.items

        # 记录用户浏览历史
        if session.get('user_id') and houses:
            user = UserActual.query.get(session['user_id'])
            if user:
                # 获取当前浏览的房源ID
                current_seen_ids = [str(house.id) for house in houses]
                
                # 获取用户原有的浏览历史
                existing_seen_ids = user.seen_id.split(',') if user.seen_id else []
                
                # 合并并去重，保留最新的浏览记录在前面
                updated_seen_ids = current_seen_ids + [id for id in existing_seen_ids if id not in current_seen_ids]
                
                # 限制浏览历史最多保存100条记录
                updated_seen_ids = updated_seen_ids[:100]
                
                # 更新用户的浏览历史
                user.seen_id = ','.join(updated_seen_ids)
                db.session.commit()

        return render_template('list.html', houses=houses, pagination=pagination, 
                             region=region, house_type=house_type, 
                             min_price=min_price, max_price=max_price)

    # GET 请求显示搜索页面
    return render_template('search_list.html')

@houses_bp.route('/house/detail/<int:house_id>')
def house_detail(house_id):
    house = HouseInfoFinal.query.get_or_404(house_id)
    
    # 记录用户浏览历史
    if session.get('user_id'):
        user = UserActual.query.get(session['user_id'])
        if user:
            # 获取用户原有的浏览历史
            existing_seen_ids = user.seen_id.split(',') if user.seen_id else []
            
            # 将当前房源ID添加到浏览历史的最前面
            updated_seen_ids = [str(house_id)] + [id for id in existing_seen_ids if id != str(house_id)]
            
            # 限制浏览历史最多保存100条记录
            updated_seen_ids = updated_seen_ids[:100]
            
            # 更新用户的浏览历史
            user.seen_id = ','.join(updated_seen_ids)
            db.session.commit()
    
    # 检查用户是否已收藏该房源
    is_collected = False
    if session.get('user_id'):
        user = UserActual.query.get(session['user_id'])
        if user and user.collect_id:
            collect_ids = user.collect_id.split(',')
            is_collected = str(house_id) in collect_ids
    
    return render_template('detail_page.html', house=house, is_collected=is_collected)

@houses_bp.route('/house/more_beijing')
def more_beijing():
    # 查询北京市的房源，按总价降序排列，取前10条
    houses = HouseInfoFinal.query.filter(
        HouseInfoFinal.region.like('%北京%')
    ).order_by(HouseInfoFinal.total_price.desc()).limit(10).all()
    
    return render_template('list.html', houses=houses, title="更多北京房源推荐")

@houses_bp.route('/house/more_hot')
def more_hot():
    # 查询热门房源（这里简单地按总价降序排列，取前10条作为示例）
    houses = HouseInfoFinal.query.order_by(HouseInfoFinal.total_price.desc()).limit(10).all()
    
    return render_template('list.html', houses=houses, title="更多热点房源推荐")

# 新增的API接口用于提供房源统计数据
@houses_bp.route('/api/house/statistics')
def house_statistics():
    try:
        # 获取区域统计（前10个区域）
        region_stats = db.session.query(
            HouseInfoFinal.region,
            func.count(HouseInfoFinal.id).label('count')
        ).group_by(HouseInfoFinal.region).order_by(func.count(HouseInfoFinal.id).desc()).limit(10).all()
        
        # 获取户型统计（前10个户型）
        house_type_stats = db.session.query(
            HouseInfoFinal.house_type,
            func.count(HouseInfoFinal.id).label('count')
        ).group_by(HouseInfoFinal.house_type).order_by(func.count(HouseInfoFinal.id).desc()).limit(10).all()
        
        # 获取价格走势（按区域分组的平均价格）
        price_trend = db.session.query(
            HouseInfoFinal.region,
            func.avg(HouseInfoFinal.price).label('avg_price')
        ).group_by(HouseInfoFinal.region).order_by(func.avg(HouseInfoFinal.price).desc()).limit(10).all()
        
        # 构造返回数据
        data = {
            'region_stats': [{'name': stat.region, 'value': stat.count} for stat in region_stats],
            'house_type_stats': [{'name': stat.house_type, 'value': stat.count} for stat in house_type_stats],
            'price_trend': [{'name': stat.region, 'value': float(stat.avg_price) if stat.avg_price else 0} for stat in price_trend]
        }
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取统计数据失败：{str(e)}'}), 500

# 新增API接口：获取小区房源数量TOP20
@houses_bp.route('/api/community/stats')
def community_stats():
    try:
        # 查询小区房源数量统计（TOP20）
        stats = db.session.query(
            HouseInfoFinal.communityName,
            func.count(HouseInfoFinal.id).label('count')
        ).group_by(HouseInfoFinal.communityName).order_by(func.count(HouseInfoFinal.id).desc()).limit(20).all()
        
        # 构造返回数据
        data = [{'name': stat.communityName, 'value': stat.count} for stat in stats]
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取小区统计失败：{str(e)}'}), 500

# 新增API接口：获取户型占比统计
@houses_bp.route('/api/house_type/proportion')
def house_type_proportion():
    try:
        # 查询户型占比统计
        stats = db.session.query(
            HouseInfoFinal.house_type,
            func.count(HouseInfoFinal.id).label('count')
        ).group_by(HouseInfoFinal.house_type).all()
        
        # 计算总数
        total_count = sum([stat.count for stat in stats])
        
        # 构造返回数据（百分比）
        data = []
        for stat in stats:
            percentage = round((stat.count / total_count) * 100, 2) if total_count > 0 else 0
            data.append({
                'name': stat.house_type,
                'value': percentage
            })
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取户型占比失败：{str(e)}'}), 500

# 新增API接口：获取户型价格走势统计
@houses_bp.route('/api/house_type/price_trend')
def house_type_price_trend():
    try:
        # 查询不同户型的平均价格
        stats = db.session.query(
            HouseInfoFinal.house_type,
            func.avg(HouseInfoFinal.price).label('avg_price')
        ).group_by(HouseInfoFinal.house_type).all()
        
        # 构造返回数据
        data = [{'name': stat.house_type, 'value': float(stat.avg_price) if stat.avg_price else 0} for stat in stats]
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取户型价格走势失败：{str(e)}'}), 500

# 新增API接口：获取区域价格走势统计
@houses_bp.route('/api/region/price_trend')
def region_price_trend():
    try:
        # 查询不同区域的平均价格
        stats = db.session.query(
            HouseInfoFinal.region,
            func.avg(HouseInfoFinal.price).label('avg_price')
        ).group_by(HouseInfoFinal.region).all()
        
        # 构造返回数据
        data = [{'name': stat.region, 'value': float(stat.avg_price) if stat.avg_price else 0} for stat in stats]
        
        return jsonify({'success': True, 'data': data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取区域价格走势失败：{str(e)}'}), 500